package com.google.code.sms.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

import com.google.code.sms.Logger;
import com.google.code.sms.service.metadata.MetaData;

/**
 * Provides database services for audio meta-data.
 *
 * @author Scott Ware
 */

public class AudioMetaDataDao extends AbstractDao {
    
    private static final Logger LOG = Logger.getLogger("AudioMetaDataDao");
    private static final String COLUMNS = "media_element_id, title, artist, album_artist, album, comment, genre, year, disc_number, disc_subtitle, track_number, bit_rate, duration, cover_art_path, codec, sample_rate, configuration";
    private final RowMapper rowMapper = new AudioMetaDataDao.AudioMetaDataMapper();

    /**
     * Returns the audio meta-data for the given media element ID.
     *
     * @param id The ID.
     * @return The audio element or null.
     */
    public MetaData getMetaData(int id) {
        return queryOne("select " + COLUMNS + " from audio_metadata where media_element_id=?", rowMapper, id);
    }
    
     /**
     * Creates or updates an audio meta-data entry.
     *
     * @param metadata The audio meta-data to create/update.
     */
    public void createOrUpdate(MetaData metadata) {
        
        if(metadata.getMediaElementID() == null)
        {
            LOG.warn("Media Element ID not defined");
            return;
        }
        
        //Check that media_element_ID exists before creating/updating
        String sql = "update audio_metadata set "
                + "title=?,"
                + "artist=?,"
                + "album_artist=?,"
                + "album=?,"
                + "comment=?,"
                + "genre=?,"
                + "year=?,"
                + "disc_number=?,"
                + "disc_subtitle=?,"
                + "track_number=?,"
                + "bit_rate=?,"
                + "duration=?,"
                + "cover_art_path=?,"
                + "codec=?,"
                + "sample_rate=?,"
                + "configuration=?"
                + "where media_element_id=?";

        int n = update(sql, metadata.getTitle(), metadata.getArtist(), metadata.getAlbumArtist(), metadata.getAlbum(), metadata.getComment(), metadata.getGenre(), metadata.getYear(), metadata.getDiscNumber(), metadata.getDiscSubtitle(), metadata.getTrackNumber(),
                    metadata.getBitRate(), metadata.getDuration(), metadata.getCoverArtPath(), metadata.getAudioCodec(), metadata.getAudioSampleRate(), metadata.getAudioConfiguration(), metadata.getMediaElementID());


        if (n == 0) {

            update("insert into audio_metadata (" + COLUMNS + ") values (" + questionMarks(COLUMNS) + ")",
                    metadata.getMediaElementID(), metadata.getTitle(), metadata.getArtist(), metadata.getAlbumArtist(), metadata.getAlbum(), metadata.getComment(), metadata.getGenre(), metadata.getYear(), metadata.getDiscNumber(), metadata.getDiscSubtitle(), metadata.getTrackNumber(),
                    metadata.getBitRate(), metadata.getDuration(), metadata.getCoverArtPath(), metadata.getAudioCodec(), metadata.getAudioSampleRate(), metadata.getAudioConfiguration());
        }
    }

    public void deleteMetaData(int id) {
        update("delete from audio_metadata where media_element_id=?", id);
        update("checkpoint");
    }
    
    public List<String> getArtists() {
        return queryForStrings("select distinct artist from audio_metadata where artist is not null order by artist");
    }
    
    public List<String> getAlbumArtists() {
        return queryForStrings("select distinct album_artist from audio_metadata where album_artist is not null order by album_artist");
    }
    
    public List<String> getAlbums() {
        return queryForStrings("select distinct album from audio_metadata where album is not null order by album");
    }
    
    public List<String> getGenres() {
        return queryForStrings("select distinct genre from audio_metadata where genre is not null order by genre");
    }
    
    private static class AudioMetaDataMapper implements ParameterizedRowMapper<MetaData> {

        @Override
        public MetaData mapRow(ResultSet rs, int rowNum) throws SQLException 
        {
            MetaData metadata = new MetaData();
            
            metadata.setMediaElementID(rs.getInt(1));
            metadata.setTitle(rs.getString(2));
            metadata.setArtist(rs.getString(3));
            metadata.setAlbumArtist(rs.getString(4));
            metadata.setAlbum(rs.getString(5));
            metadata.setComment(rs.getString(6));
            metadata.setGenre(rs.getString(7));
            metadata.setYear(rs.getInt(8));
            metadata.setDiscNumber(rs.getInt(9) == 0 ? null : rs.getInt(9));
            metadata.setDiscSubtitle(rs.getString(10));
            metadata.setTrackNumber(rs.getInt(11) == 0 ? null : rs.getInt(11));
            metadata.setBitRate(rs.getInt(12) == 0 ? null : rs.getInt(12));
            metadata.setDuration(rs.getInt(13) == 0 ? null : rs.getInt(13));
            metadata.setCoverArtPath(rs.getString(14));
            metadata.setAudioCodec(rs.getString(15));
            metadata.setAudioSampleRate(rs.getInt(16));
            metadata.setAudioConfiguration(rs.getString(17));
            
            return metadata;
        }
    }
    
    public static RowMapper getRowMapper()
    {
        return new AudioMetaDataDao.AudioMetaDataMapper();
    }
}
